package db;

import java.sql.*;
import java.util.*;

import util.*;
import db.DBConnection;
import model.Apartment;
import model.ApartmentStatus;
import model.ApartmentType;
import model.Rent;
import model.Renter;
import model.RenterStatus;
import model.ReportEvaluation;
import model.ReportType;

public class DBApartment implements IFDBApartment
{
	/* FIELDS */
	private Connection con;
	private QueryBuilder qb;

	/* CONSTRUCTORS */
	public DBApartment()
	{
		con = DBConnection.getInstance().getDBcon();
		qb = new QueryBuilder("Apartment", new String[]
		/* custom */
		{ "id", "nr", "address", "status", "appartmentTypeId" });
		/* end custom */
	}

	/* BUILDERS */
	private ArrayList<Object> getValues(Apartment item)
	{
		ArrayList<Object> l = new ArrayList<Object>();
		/* custom */
		l.add(item.getId());
		l.add(item.getNr());
		l.add(item.getAddress());
		// System.out.println("GET VALUES: " + item.getStatus().getValue());
		l.add(item.getStatus().getIndex());
		l.add(item.getType().getId());
		/* end custom */
		return l;
	}

	private Apartment buildObject(ResultSet results)
	{
		Apartment item = new Apartment();
		try
		{
			/* custom */
			item.setId(results.getInt("id"));
			item.setNr(results.getInt("nr"));
			item.setAddress(results.getString("address"));
			item.setStatus(ApartmentStatus.getStatus(results.getInt("status")));
			item.setType(new ApartmentType(results.getInt("appartmentTypeId")));

			// added 08-01-2015
			// System.out.print("Veiw select rent ID:"+results.getInt("rentId"));
			int rentId = results.getInt("rentId");
			Renter renter = new Renter();
			Rent rent = new Rent();
			if (!item.getStatus().equals(ApartmentStatus.EMPTY))
			{
				

				renter.setId(results.getInt("renterId"));
				renter.setFirstName(results.getString("firstName"));
				renter.setLastName(results.getString("lastName"));
				renter.setCpr(results.getString("cpr"));
				renter.setMobilePhone(results.getString("mobilePhone"));
				renter.setPrivatePhone(results.getString("privatePhone"));
				renter.setAddress(results.getString("address"));
				renter.setEmail(results.getString("email"));
				renter.setOccupation(results.getString("occupation"));
				renter.setWorkplace(results.getString("workplace"));
				renter.setStatus(RenterStatus.getStatus(results.getInt("renterStatus")));
				
				rent.setId(results.getInt("rentId"));
				rent.setMoveInDate(qb.toUtil(results.getDate("moveInDate")));
				rent.setMoveOutDate(qb.toUtil(results.getDate("moveOutDate")));
				rent.setApartment(new Apartment(results.getInt("id")));
			}
			rent.setRenter(renter);
			item.setRent(rent);
			/* end custom */
		} catch (Exception e)
		{
			System.out.println("error in building object");
		}
		return item;
	}

	private Apartment selectSingle(String condition)
	{
		PreparedStatement ps = null;
		Apartment item = new Apartment();
		String select = qb.selectQuery(condition);
		try
		{
			ps = con.prepareStatement(select);
			ResultSet rs = ps.executeQuery();
			while (rs.next())
			{
				item = this.buildObject(rs);
			}
		} catch (SQLException e)
		{
			e.printStackTrace();
		} finally
		{
			if (ps != null)
			{
				try
				{
					ps.close();
				} catch (SQLException e)
				{
					e.printStackTrace();
				}
			}
		}
		return item;
	}

	private ArrayList<Apartment> selectMultiple(String condition)
	{
		ArrayList<Apartment> list = new ArrayList<Apartment>();
		PreparedStatement ps = null;
		String select = qb.selectQuery(condition);
		try
		{
			ps = con.prepareStatement(select);
			ResultSet rs = ps.executeQuery();
			while (rs.next())
			{
				Apartment item = new Apartment();
				item = this.buildObject(rs);
				list.add(item);
			}
		} catch (SQLException e)
		{
			e.printStackTrace();
		} finally
		{
			if (ps != null)
			{
				try
				{
					ps.close();
				} catch (SQLException e)
				{
					e.printStackTrace();
				}
			}
		}
		return list;
	}

	// added 08-01-2015
	private ArrayList<Apartment> selectView()
	{
		ArrayList<Apartment> list = new ArrayList<Apartment>();
		PreparedStatement ps = null;

		String select = "select  "
				+ "a.id, a.nr, a.address, a.appartmentTypeId, a.status, "
				+ "r.Id as rentId, r.moveInDate, r.moveOutDate, r.renterId,"
				+ "re.firstName, re.lastName,re.cpr,re.mobilePhone,"
				+ "re.privatePhone,re.address,re.email,"
				+ "re.occupation,re.workplace,re.status as renterStatus "
				+ "from apartment a left outer join rent r on r.apartmentId=a.id "
				+ "left outer join renter re on r.renterId = re.id";

		// String select ="select "
		// +"i.id as installationId, i.serialNumber, i.garantyExpDate, i.type as installationType, i.installDate, i.apartmentId,"
		// +"RMO.id as rmoId, rmo.condition as rmoCondition, rmo.rentId as rmoRentId, "
		// +"rmi.id as rmiId, rmi.condition as rmiCondition, rmi.rentId as rmiRentId "
		// +"from Installation i  "
		// + "left outer join Report RMI on rmi.installationID = i.id "
		// + "left outer join Report RMO on rmo.installationID = I.id "
		// +"where I.apartmentID = " + apartmentId + " "
		// +"and RMI.Type = " + ReportType.MOVEIN.getIndex() + " "
		// +"and RMO.Type = " + ReportType.MOVEOUT.getIndex() + " "
		// +"and RMO.rentId = " + rentId + " "
		// +"and RMO.rentId = " + rentId + " ";

		try
		{
			ps = con.prepareStatement(select);
			ResultSet rs = ps.executeQuery();
			while (rs.next())
			{
				Apartment item = new Apartment();
				item = this.buildObject(rs);
				list.add(item);
			}
		} catch (SQLException e)
		{
			e.printStackTrace();
		} finally
		{
			if (ps != null)
			{
				try
				{
					ps.close();
				} catch (SQLException e)
				{
					e.printStackTrace();
				}
			}
		}
		return list;
	}

	@Override
	public ArrayList<Apartment> getAll()
	{
		return this.selectMultiple("");
	}

	@Override
	// added 08-01-2015
	public ArrayList<Apartment> getAllWithRenters()
	{
		// return
		// this.selectMultiple("left outer join renter as r on apartment.renterId = r.Id");
		return this.selectView();

	}

	@Override
	public Apartment getById(int id)
	{
		String condition = "where id=" + id;
		return this.selectSingle(condition);
	}

	/*
	 * @Override public ArrayList<Apartment> getByName(String name) { String
	 * condition = "where name='" + name + "'"; return
	 * this.selectMultiple(condition); }
	 */
	@Override
	public int insertScope(Apartment item)
	{
		PreparedStatement ps = null;
		int id = 0;
		try
		{
			ps = con.prepareStatement(qb.insertQuery(), Statement.RETURN_GENERATED_KEYS);
			ArrayList<Object> values = this.getValues(item);
			/* Prepare all defined values except auto increment at position 0 */
			int i = 0;
			for (Object o : values)
			{
				// System.out.println("Preparing object: "+o);
				// System.out.println("Object index: "+i);
				if (i > 0 && i < values.size())
				{
					qb.setObject(ps, i, o);
				}
				i++;
			}
			int affectedRows = ps.executeUpdate();
			// System.out.println(ps.getResultSet());
			if (affectedRows == 0)
			{
				throw new SQLException("Insert failed, no rows affected.");
			}

			try (ResultSet generatedKeys = ps.getGeneratedKeys())
			{
				if (generatedKeys.next())
				{
					id = generatedKeys.getInt(1);
					System.out.println("Generated key:" + generatedKeys.getInt(1));
				} else
				{
					throw new SQLException("Insert, no ID obtained.");
				}
			}
		} catch (SQLException e)
		{
			e.printStackTrace();
		} finally
		{
			if (ps != null)
			{
				try
				{
					ps.close();
				} catch (SQLException e)
				{
					e.printStackTrace();
				}
			}
		}
		return id;
	}

	@Override
	public void update(Apartment item)
	{
		PreparedStatement ps = null;
		try
		{
			con.setAutoCommit(false);
			ps = con.prepareStatement(qb.updateQuery("WHERE id=?"));
			ArrayList<Object> values = this.getValues(item);

			int i = 0;
			for (Object o : values)
			{
				if (i > 0 && i <= values.size())
				{
					qb.setObject(ps, i, o);
				}
				i++;
			}
			qb.setObject(ps, values.size(), values.get(0));

			ps.executeUpdate();
			con.commit();
		} catch (SQLException e)
		{
			if (con != null)
			{
				try
				{
					System.err.println("Transaction is being rolled back");
					con.rollback();
				} catch (SQLException sqle)
				{
					sqle.printStackTrace();
				}
			}
			e.printStackTrace();
		} finally
		{
			if (ps != null)
			{
				try
				{
					ps.close();
					con.setAutoCommit(true);
				} catch (SQLException e)
				{
					e.printStackTrace();
				}
			}
		}

	}

	@Override
	public void delete(Apartment item)
	{
		PreparedStatement ps = null;
		try
		{
			con.setAutoCommit(false);
			ps = con.prepareStatement(qb.deleteQuery("WHERE id=?"));
			ps.setInt(1, item.getId());
			ps.executeUpdate();
			con.commit();
		} catch (SQLException e)
		{
			if (con != null)
			{
				try
				{
					System.err.print("Transaction is being rolled back");
					con.rollback();
				} catch (SQLException sqle)
				{
					sqle.printStackTrace();
				}
			}
			e.printStackTrace();
		} finally
		{
			if (ps != null)
			{
				try
				{
					ps.close();
					con.setAutoCommit(true);
				} catch (SQLException e)
				{
					e.printStackTrace();
				}
			}
		}

	}
}
